﻿using SuperiorModel;
using SuperiorSqlTools;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace SuperiorShopDataAccess
{
    public class ProductDataAccess
    {
        public static DataSet ProductList(ProductCriteria criteria)
        {
            var sqlManager = new SqlManager();
            List<SqlParameter> parms = new List<SqlParameter>();
            var sb = sqlManager.CreateSb();
            if (!string.IsNullOrEmpty(criteria.ProductName))
            {
                sb.Append(" and p.ProductName=@productName");
                parms.Add(new SqlParameter("@productName", criteria.ProductName));
            }
            if (!string.IsNullOrEmpty(criteria.MenuId))
            {
                sb.Append(" and p.MenuId=@menuId ");
                parms.Add(new SqlParameter("@menuId", criteria.MenuId.ToInt()));
            }
            if (!string.IsNullOrEmpty(criteria.LoginName))
            {
                sb.Append(" and s.LoginName=@LoginName ");
                parms.Add(new SqlParameter("@LoginName", criteria.LoginName));
            }
            if (criteria.ShopAdminId != 0)
            {
                sb.Append(" and p.ShopAdminId=@shopid ");
                parms.Add(new SqlParameter("@shopid", criteria.ShopAdminId));
            }
            if (!string.IsNullOrEmpty(criteria.Brand))
            {
                sb.Append(" and p.Brand=@brand ");
                parms.Add(new SqlParameter("@brand", criteria.Brand));
            }
            if (criteria.OptionStatus != 999)
            {
                sb.Append(" and p.OptionStatus=@optionStatus ");
                parms.Add(new SqlParameter("@optionStatus", criteria.OptionStatus));
            }
            var sql = string.Format("select top({0}) * from(select ROW_NUMBER() over(order by p.Id desc)as rownum,p.*,ISNULL(m.MenuName,'') as MenuName,cpi.ImagePath,s.LoginName  from C_Product p left join C_Menu m on p.MenuId=m.Id left join C_ProductImg cpi on p.Id=cpi.ProductId and cpi.IsDefault=1 and cpi.IsDel=0 left join C_ShopAdmin s on p.ShopAdminId=s.Id  where {1} and p.IsDel=0 )tt where tt.rownum>{2};select count(*) as totalCount from C_Product p left join C_ShopAdmin s on p.ShopAdminId=s.Id where {1} and IsDel=0", criteria.PagingResult.PageSize, sb.ToString(), criteria.PagingResult.PageSize * criteria.PagingResult.PageIndex);
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms.ToArray());
        }

        public static DataSet GetProductBannerList(int shopAdminId)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
           {
                new SqlParameter("@shopAdminId",shopAdminId)
            };
            var sql = "select * from C_BannerIndex where ShopAdminId=@shopAdminId and IsDel=0 order by Id desc";
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms);
        }

        public static DataSet GetProductBanner(int id)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
           {
                new SqlParameter("@id",id)
            };
            var sql = "select * from C_BannerIndex where Id=@id";
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms);
        }
        public static DataSet MenuList(int shopAdminId)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
           {
                new SqlParameter("@shopAdminId",shopAdminId)
            };
            var sql = "select * from C_Menu where IsDel=0 and ShopAdminId =@shopAdminId order by Id desc";
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms);
        }

        public static int AddMenu(MenuModel model)
        {
            var sqlManager = new SqlManager();
            SqlParameter rtn_err = sqlManager.GetRtnParameter();
            SqlParameter[] parms =
            {
                new SqlParameter("@menuName", model.MenuName),
                new SqlParameter("@shopAdminId",model.ShopAdminId),
                new SqlParameter("@sorft",model.Sorft),
                new SqlParameter("@isAll",model.IsAll),
                new SqlParameter("@imgpath",model.ImgPath),
                rtn_err
            };
            sqlManager.ExecuteNonQuery(CommandType.StoredProcedure, "InsertMenu", parms);
            if (rtn_err.Value != null)
            {
                return int.Parse(rtn_err.Value.ToString());
            }
            return -1;
        }

        public static bool AddProduct(ProductModel model, List<ProductImgModel> imgs)
        {
            var res = true;
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@ShopAdminId", model.ShopAdminId),
                new SqlParameter("@ProductName",model.ProductName),
                new SqlParameter("@Brand", model.Brand),
                new SqlParameter("@Title", model.Title),
                new SqlParameter("@MenuId", model.MenuId),
                new SqlParameter("@IsHot", model.IsHot),
                new SqlParameter("@ShipTemplateId", model.ShipTemplateId),
                new SqlParameter("@Unit", model.Unit),
                new SqlParameter("@Sorft", model.Sorft),
                new SqlParameter("@InitSallNum", model.InitSallNum),
                new SqlParameter("@ProductDetail",model.Detail)

            };
            var p_sql = "insert into C_Product(ShopAdminId,ProductName,Brand,Title,MenuId,Unit,Sorft,InitSallNum,ProductDetail,ShippingTemplateId,IsHot)values(@ShopAdminId,@ProductName,@Brand,@Title,@MenuId,@Unit,@Sorft,@InitSallNum,@ProductDetail,@ShipTemplateId,@IsHot);select @@IDENTITY";
            object o = sqlManager.ExecuteScalar(p_sql, parms);
            if (o != null && o != DBNull.Value)
            {
                var newid = Convert.ToInt32(o);
                //--开始插入商品图片事务
                var sqlList = new List<string>();
                imgs.ForEach((item) =>
                {
                    sqlList.Add(string.Format("insert into C_ProductImg(ImagePath,ImageType,ProductId,IsDefault)values('{0}',1,{1},{2})", item.ImagePath, newid, item.IsDefault));
                });
                var r = sqlManager.ExecTransactionAsUpdate(sqlList.ToArray(), null);
                //如果事务失败，将商品删除
                if (!r)
                {
                    res = false;
                    var del_sql = "update C_Product set IsDel=1 where Id=" + newid;
                    sqlManager.ExecuteNonQuery(CommandType.Text, del_sql, null);
                }
            }
            else
            {
                res = false;
            }
            return res;
        }

        public static bool AddProductBanner(ProductBannerModel model)
        {
            var res = true;
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@ProductId",model.ProductId),
                new SqlParameter("@ImgPath", model.ImgPath),
                new SqlParameter("@Sorft", model.Sorft),
                new SqlParameter("@ShopAdminId", model.ShopAdminId)
            };
            var sql = "insert into C_BannerIndex(ProductId,ImgPath,Sorft,ShopAdminId)values(@ProductId,@ImgPath,@Sorft,@ShopAdminId)";
            var ret = sqlManager.ExecuteNonQuery(CommandType.Text, sql, parms);
            if (ret != 1)
                res = false;
            return res;
        }

        public static bool EditProductBanner(ProductBannerModel model)
        {
            var res = true;
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@ProductId",model.ProductId),
                new SqlParameter("@ImgPath", model.ImgPath),
                new SqlParameter("@Sorft", model.Sorft),
                new SqlParameter("@Id", model.Id)
            };
            var sql = "update C_BannerIndex set ProductId=@ProductId,ImgPath=@ImgPath,Sorft=@Sorft where Id=@Id";
            var ret = sqlManager.ExecuteNonQuery(CommandType.Text, sql, parms);
            if (ret != 1)
                res = false;
            return res;
        }

        public static void OptionProductBanner(int id, int optionStatus)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@OptionStatus", optionStatus),
                new SqlParameter("@Id",id)
            };
            var sql = "update C_BannerIndex set OptionStatus=@OptionStatus where Id=@Id";
            var ret = sqlManager.ExecuteNonQuery(CommandType.Text, sql, parms);
        }

        public static void DelProductBanner(int id)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@Id",id)
            };
            var sql = "update C_BannerIndex set IsDel=1 where Id=@Id";
            var ret = sqlManager.ExecuteNonQuery(CommandType.Text, sql, parms);
        }

        public static bool EditProduct(ProductModel model, List<ProductImgModel> imgs)
        {
            var res = true;
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@id", model.Id),
                new SqlParameter("@ProductName",model.ProductName),
                new SqlParameter("@Brand", model.Brand),
                new SqlParameter("@Title", model.Title),
                new SqlParameter("@MenuId", model.MenuId),
                new SqlParameter("@IsHot", model.IsHot),
                new SqlParameter("@ShipTemplateId", model.ShipTemplateId),
                new SqlParameter("@Unit", model.Unit),
                new SqlParameter("@Sorft", model.Sorft),
                new SqlParameter("@InitSallNum", model.InitSallNum),
                new SqlParameter("@ProductDetail",model.Detail)

            };
            //--更新商品主体和图片，放到一个事务中
            var p_sql = "update C_Product set ProductName=@ProductName,Brand=@Brand,Title=@Title,MenuId=@MenuId,Unit=@Unit,UpdateTime=GETDATE(),Sorft=@Sorft,InitSallNum=@InitSallNum,ProductDetail=@ProductDetail,ShippingTemplateId=@ShipTemplateId,IsHot=@IsHot where Id=@id and IsDel=0;update C_ProductImg set IsDel=1 where ProductId=@id and IsDel=0";
            var _parms = new List<SqlParameter[]>();
            _parms.Add(parms);
            var sqlList = new List<string>();
            sqlList.Add(p_sql);
            imgs.ForEach((item) =>
            {
                sqlList.Add(string.Format("insert into C_ProductImg(ImagePath,ImageType,ProductId,IsDefault)values('{0}',1,{1},{2})", item.ImagePath, model.Id, item.IsDefault));
                //事务的封装，如果需要参数化的话，要给sql数组每个sql一个参数化数组，空的也行
                _parms.Add(new List<SqlParameter>().ToArray());
            });

            var r = sqlManager.ExecTransactionAsUpdate(sqlList.ToArray(), _parms);
            //如果事务失败，将商品删除
            if (!r)
            {
                res = false;
            }

            return res;
        }

        public static int EditMenu(MenuModel model)
        {
            var sqlManager = new SqlManager();
            SqlParameter rtn_err = sqlManager.GetRtnParameter();
            SqlParameter[] parms =
            {
                new SqlParameter("@id", model.Id),
                new SqlParameter("@menuName",model.MenuName),
                new SqlParameter("@sorft",model.Sorft),
                new SqlParameter("@isAll",model.IsAll),
                new SqlParameter("@imgpath",model.ImgPath),
                rtn_err
            };
            sqlManager.ExecuteNonQuery(CommandType.StoredProcedure, "EditMenu", parms);
            if (rtn_err.Value != null)
            {
                return int.Parse(rtn_err.Value.ToString());
            }
            return -1;
        }


        public static int OptionMenu(int id, int optionStatus, int shopAdminId)
        {
            var sqlManager = new SqlManager();
            SqlParameter rtn_err = sqlManager.GetRtnParameter();
            SqlParameter[] parms =
            {
                new SqlParameter("@id", id),
                new SqlParameter("@optionStatus",optionStatus),
                new SqlParameter("@shopAdminId",shopAdminId),
                rtn_err
            };
            sqlManager.ExecuteNonQuery(CommandType.StoredProcedure, "[OptionMenu]", parms);
            if (rtn_err.Value != null)
            {
                return int.Parse(rtn_err.Value.ToString());
            }
            return -1;
        }

        public static int OptionProduct(int id, int optionStatus, int shopAdminId)
        {
            var sqlManager = new SqlManager();
            SqlParameter rtn_err = sqlManager.GetRtnParameter();
            SqlParameter[] parms =
            {
                new SqlParameter("@id", id),
                new SqlParameter("@optionStatus",optionStatus),
                new SqlParameter("@shopAdminId",shopAdminId),
                rtn_err
            };
            sqlManager.ExecuteNonQuery(CommandType.StoredProcedure, "[OptionProduct]", parms);
            if (rtn_err.Value != null)
            {
                return int.Parse(rtn_err.Value.ToString());
            }
            return -1;
        }

        public static int DeleteProduct(int id, int shopAdminId)
        {
            var sqlManager = new SqlManager();
            SqlParameter rtn_err = sqlManager.GetRtnParameter();
            SqlParameter[] parms =
            {
                new SqlParameter("@id", id),
                new SqlParameter("@shopAdminId", shopAdminId),
                rtn_err
            };
            sqlManager.ExecuteNonQuery(CommandType.StoredProcedure, "[DeleteProduct]", parms);
            if (rtn_err.Value != null)
            {
                return int.Parse(rtn_err.Value.ToString());
            }
            return -1;
        }

        public static int AddPropety(AddPropetyParamsModel model)
        {
            var sqlManager = new SqlManager();
            SqlParameter rtn_err = sqlManager.GetRtnParameter();
            SqlParameter[] parms =
            {
                new SqlParameter("@PropetyName", model.PropetyName),
                new SqlParameter("@PropetyValue", model.PropetyValue),
                 new SqlParameter("@ProductId", model.ProductId.ToInt()),
                  new SqlParameter("@ShopAdminId", model.ShopAdminId),
                rtn_err
            };
            sqlManager.ExecuteNonQuery(CommandType.StoredProcedure, "[AddPropety]", parms);
            if (rtn_err.Value != null)
            {
                return int.Parse(rtn_err.Value.ToString());
            }
            return -1;
        }

        public static int AddPropetyValue(Product_Sall_Propety_ValueModel model)
        {
            var sqlManager = new SqlManager();
            SqlParameter rtn_err = sqlManager.GetRtnParameter();
            SqlParameter[] parms =
            {
                new SqlParameter("@SallPropetyId", model.SallPropetyId),
                new SqlParameter("@PropetyValue", model.PropetyValue),
                 new SqlParameter("@ProductId", model.ProductId.ToInt()),
                rtn_err
            };
            sqlManager.ExecuteNonQuery(CommandType.StoredProcedure, "[AddPropetyValue]", parms);
            if (rtn_err.Value != null)
            {
                return int.Parse(rtn_err.Value.ToString());
            }
            return -1;
        }

        public static int DeleteMenu(int id, int shopAdminId)
        {
            var sqlManager = new SqlManager();
            SqlParameter rtn_err = sqlManager.GetRtnParameter();
            SqlParameter[] parms =
            {
                new SqlParameter("@id", id),
                new SqlParameter("@shopAdminId", shopAdminId),
                rtn_err
            };
            sqlManager.ExecuteNonQuery(CommandType.StoredProcedure, "[DeleteMenu]", parms);
            if (rtn_err.Value != null)
            {
                return int.Parse(rtn_err.Value.ToString());
            }
            return -1;
        }

        public static bool EditMoreSku(ProductSkuManagerModel model, int shopAdminId)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@ProductId",model.ProductId.ToInt()),
                new SqlParameter("@ShopAdminId",shopAdminId)
            };
            var sqlList = new List<string>();
            //将商品当前规格类型改为多规格
            sqlList.Add("update C_Product set CurrentSkuType = 2 where Id = @ProductId and IsDel = 0 and ShopAdminId = @ShopAdminId;");
            //删除当前商品所有SKU
            sqlList.Add("update C_Product_Sku set IsDel = 1 where ProductId=@ProductId and IsDel=0 and ShopAdminId=@ShopAdminId and SkuType=2;");
            //循环插入新SKU
            var _pid = model.ProductId.ToInt();
            model.MoreSkuModelList.ForEach((sku) =>
            {
                sqlList.Add(string.Format("insert into C_Product_Sku(PropetyCombineId,ProppetyCombineName,ProductId,StockNum,SallPrice,SkuType,SkuCode,ShopAdminId,Weight)values('{0}','{1}',{2},{3},{4},2,'{5}',{6},{7});", sku.PropetyCombineId, sku.ProppetyCombineName, _pid, sku.StockNum, sku.SallPrice, sku.SkuCode, shopAdminId, sku.Weight));
            });
            var ret = sqlManager.ExecTransactionAsUpdate_Public(sqlList.ToArray(), parms);
            return ret;
        }

        public static bool EditSingleSku(SingleSkuModel model)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@ProductId",model.ProductId),
                new SqlParameter("@ShopAdminId",model.ShopAdminId),
                new SqlParameter("@StockNum",model.StockNum),
                new SqlParameter("@SallPrice",model.SallPrice),
                new SqlParameter("@Weight",model.Weight),
                new SqlParameter("@SkuCode",model.SkuCode)//这里注意，由于这个参数可能为NULL，直接扔存储过程没事，SQL文本的话，需要给默认""值。
            };
            var sb = new StringBuilder();
            sb.Append("if exists(select Id from C_Product_Sku where ProductId=@ProductId and ShopAdminId=@ShopAdminId and SkuType=1 and IsDel=0)");
            sb.Append(" begin ");
            sb.Append(" update C_Product_Sku set StockNum=@StockNum,SallPrice=@SallPrice,SkuCode=@SkuCode,Weight=@Weight where ProductId=@ProductId and ShopAdminId=@ShopAdminId and SkuType=1 and IsDel=0 ");
            sb.Append(" end else begin ");
            sb.Append("insert into C_Product_Sku(ProductId,StockNum,SallPrice,SkuType,SkuCode,ShopAdminId,Weight)values(@ProductId,@StockNum,@SallPrice,1,@SkuCode,@ShopAdminId,@Weight) end");
            var sqlList = new List<string>();
            sqlList.Add(sb.ToString());
            sqlList.Add(" update C_Product set CurrentSkuType=1 where Id=@ProductId");
            var ret = sqlManager.ExecTransactionAsUpdate_Public(sqlList.ToArray(), parms);
            return ret;
        }

        public static DataSet GetProduct(int id)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@id",id)
            };
            var sql = "select * from C_Product where Id = @id;select * from C_ProductImg where ProductId=@id and IsDel=0 order by IsDefault desc";
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms);
        }

        public static DataSet GetProductSkuDs(int shopAdminId, string productId)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@shopAdminId",shopAdminId),
                new SqlParameter("@productId",productId)
            };
            var sb = new StringBuilder();
            sb.Append("select * from C_Product_Sku where ProductId= @productId and ShopAdminId =@shopAdminId and SkuType=1 and IsDel=0;");

            sb.Append("select CurrentSkuType from C_Product where Id=@productId and ShopAdminId = @shopAdminId;");

            return sqlManager.ExecuteDataset(CommandType.Text, sb.ToString(), parms);
        }

        public static DataSet GetProductMoreSkuDs(int shopAdminId, string productId)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@shopAdminId",shopAdminId),
                new SqlParameter("@productId",productId)
            };
            var sb = new StringBuilder();
            sb.Append("select * from C_Product_Sku where ProductId= @productId and ShopAdminId =@shopAdminId and SkuType=2 and IsDel=0;");

            sb.Append("select * from C_Sall_Propety where ProductId=@productId and ShopAdminId=@shopAdminId and IsDel=0;");

            sb.Append("select* from C_Sall_Propety_Value where ProductId = @productId and IsDel = 0");
            return sqlManager.ExecuteDataset(CommandType.Text, sb.ToString(), parms);
        }

        public static bool DelPropetyValue(Product_Sall_Propety_ValueModel model)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@PropetyValue", model.PropetyValue),
                 new SqlParameter("@ProductId", model.ProductId.ToInt()),
                 new SqlParameter("@Id", model.Id),
            };
            var sqlList = new List<string>();
            //删除规格值逻辑：1.删除规格值表当前数据，2.删除sku表合并值包含这个值的数据
            sqlList.Add("update C_Sall_Propety_Value set IsDel = 1 where Id=@Id;");
            sqlList.Add("update C_Product_Sku set IsDel=1 where ProductId=@ProductId  and SkuType=2 and ProppetyCombineName like '%'+ @PropetyValue + '%'");
            var ret = sqlManager.ExecTransactionAsUpdate_Public(sqlList.ToArray(), parms);
            return ret;
        }
        public static bool DelPropety(int id, string productId, int shopAdminId)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@id", id),
                 new SqlParameter("@productId", productId.ToInt()),
                 new SqlParameter("@shopAdminId", shopAdminId)
            };
            var sqlList = new List<string>();

            sqlList.Add("update C_Sall_Propety set IsDel = 1 where Id=@id and ProductId=@productId and ShopAdminId=@shopAdminId;");
            sqlList.Add("update C_Sall_Propety_Value set IsDel = 1 where SallPropetyId=@id and ProductId=@productId;");
            sqlList.Add("update C_Product_Sku set IsDel = 1 where ProductId=@productId and SkuType=2;");
            var ret = sqlManager.ExecTransactionAsUpdate_Public(sqlList.ToArray(), parms);
            return ret;
        }

        public static DataSet SpecialPlaceList(int shopAdminId)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
           {
                new SqlParameter("@shopAdminId",shopAdminId)
            };
            var sql = "select s.*,m.MenuName from C_SpecialPlace s left join C_Menu m on s.MenuId=m.Id where s.ShopAdminId = @shopAdminId and s.IsDel = 0 order by Sorft ,Id desc";
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms);
        }
        public static void DelSpecialPlace(int id)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
           {
                new SqlParameter("@id",id)
            };
            var sql = "UPDATE C_SpecialPlace set IsDel = 1 where Id=@id and IsDel=0";
            sqlManager.ExecuteNonQuery(CommandType.Text, sql, parms);
        }

        public static bool InsertOrEditSpecialPlace(SpecialPlaceModel model)
        {
            var res = true;
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
             {
                new SqlParameter("@id",model.Id),
                new SqlParameter("@menuId",model.MenuId),
                new SqlParameter("@imgPath",model.ImgPath),
                new SqlParameter("@placeName",model.PlaceName),
                new SqlParameter("@optionStatus",model.OptionStatus),
                new SqlParameter("@shopAdminId",model.ShopAdminId),
                 new SqlParameter("@sorft",model.Sorft),
                  new SqlParameter("@specialType",model.SpecialType)
            };
            var sql = " if (@id>0) begin update C_SpecialPlace set MenuId = @menuId ,ImgPath = @imgPath,PlaceName = @placeName,OptionStatus = @optionStatus,Sorft=@sorft,SpecialType=@specialType where Id = @id end else begin    insert into C_SpecialPlace(MenuId, ImgPath, PlaceName, OptionStatus,ShopAdminId,Sorft,SpecialType)values(@menuId, @imgPath, @placeName, @optionStatus,@shopAdminId,@sorft,@specialType) end";
            var ret = sqlManager.ExecuteNonQuery(CommandType.Text, sql, parms);
            if (ret != 1)
                res = false;
            return res;
        }

        public static DataSet GetSpecialPlaceDs(int id)
        {
            var sqlManager = new SqlManager();
            SqlParameter[] parms =
            {
                new SqlParameter("@id",id)
            };
            var sql = "select * from C_SpecialPlace where Id=@id";
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms);
        }
    }
}
